let db = require("../../db/db")

let service = {
    getdatasources: function () {
        return [
            {
                label: "主生产计划-交期满足",
                name: "mps_order_fullfill"
            },
            {
                label: "主生产计划-产能满足",
                name: "mps_capacity_fullfill"
            },
            {
                label: "主生产计划-产能负荷",
                name: "mps_capacity_load"
            },
            {
                label: "主生产计划-产能负荷-图",
                name: "mps_capacity_load_chart"
            }
        ]
    },
    getcolumns: function (datasource) {
        if(datasource=='mps_capacity_load_chart'){
            return [
                {
                    label: "日期",
                    name: "date"
                }
            ]
        }
        else return [];
    },
    getfilterfields: function (datasource) {
        if(datasource=='mps_order_fullfill'){
            return [
                {
                    label: "目标类型", //订单or预测
                    name: "target",
                    type: "text",
                    operators: ['equal']
                },
                {
                    label: "周期类型",
                    name: "period_type",
                    type: "text",
                    operators: ['equal']
                },
                {
                    label: "期数",
                    name: "period_count",
                    type: "text",
                    operators: ['equal']
                }
            ]
        }
        else if(datasource=='mps_capacity_fullfill' || datasource=='mps_capacity_load' || datasource=='mps_capacity_load_chart'){
            return [
                {
                    label: "周期类型",
                    name: "period_type",
                    type: "text",
                    operators: ['equal']
                },
                {
                    label: "期数",
                    name: "period_count",
                    type: "text",
                    operators: ['equal']
                }
            ]
        }
    },
    getdata: async function (datasource, filter) {
        if(datasource=='mps_order_fullfill'){
            return await this.getdata_order_fullfill(filter)
        }
        else if(datasource=='mps_capacity_fullfill'){
            return await this.getdata_capacity_fullfill(filter)
        }    
        else if(datasource=='mps_capacity_load'){
            return await this.getdata_capacity_load(filter)
        }              
        else if(datasource=='mps_capacity_load_chart'){
            return await this.getdata_capacity_load_chart(filter)
        }    
    },
    getdata_order_fullfill: async function(filter){
        let target = '订单'
        let period_type = '天'
        let period_count = 10
        if (filter && filter.children) {
            let find = filter.children.find(c => c.left.field == 'target');
            if (find && find.right) target = find.right;
            find = filter.children.find(c => c.left.field == 'period_type');
            if (find && find.right) period_type = find.right;
            find = filter.children.find(c => c.left.field == 'period_count');
            if (find && find.right) period_count = find.right;
        }
        //1.根据周期分期
        let periods_set = this.get_periods_set(period_type,period_count);
        let periods = periods_set.periods;
        let total_start_day = periods_set.total_start_day;
        let total_end_day = periods_set.total_end_day;
        //2.从订单/预测中取出待交货产品列表
        let sql = "select id as product_id, code as product_code, name as product_name, stock_safe from mes_pn \
                where id in (select product_id from erp_order where status='未发货' and is_deleted=0) and is_deleted=0"
        if (target == '预测') {
            sql = "select id as product_id, code as product_code, name as product_name, stock_safe from mes_pn \
                where id in (select product_id from erp_forecast where period_type="+ db.escape(period_type) + " \
                and end_date>=CURDATE() and is_deleted=0) and is_deleted=0"
        }
        let r = await db.query(sql, []);
        let products = r.rows;
        //3.总时间范围内的预测、订单、生产计划
        let forecasts = [];
        let orders = [];
        if (target == '预测') {
            sql = "select * from erp_forecast where period_type=" + db.escape(period_type) + " and is_deleted=0 \
            and start_date>="+ db.escape(total_start_day) + "and end_date<=" + db.escape(total_end_day);
            r = await db.query(sql, []);
            forecasts = r.rows; //预测
        }
        else{
            sql = "select * from erp_order where status='未发货' and is_deleted=0 \
            and delivery_date<="+ db.escape(total_end_day);
            r = await db.query(sql, []);
            orders = r.rows; //订单  
        }      
        sql = "select * from erp_main_produce_plan where period_type=" + db.escape(period_type) + " and is_deleted=0 \
        and start_date>="+ db.escape(total_start_day) + "and delivery_date<=" + db.escape(total_end_day);
        r = await db.query(sql, []);
        let plans = r.rows; //计划

        //4.逐产品，逐周期，进行库存余量运算，余量不低于安全库存，则满足本周起交付需求
        let columns = [
            {
                label: "产品编码",
                name: "product_code"
            },
            {
                label: "产品名",
                name: "product_name"
            },
            {
                label: "数值项(" + period_type + ")",
                name: "item_name"
            }
        ]
        for (let i in periods) {
            let p = periods[i]
            let label = period_type == '月' ? (p.start.getFullYear() + '-' + (p.start.getMonth() + 1)) : ((p.start.getMonth() + 1) + '-' + p.start.getDate())
            columns.push({ label: label, name: 'p_' + i })
        }
        let rows = []
        for (let i in products) {
            let p = products[i]
            let a_ddxql = {} //订单需求量
            let a_ycxql = {} //预测需求量
            let a_mxql = {} //毛需求量=订单需求量+预测需求量
            let a_sqkcyl = {} //上期库存余量
            let a_aqkcl = {} //安全库存量
            let a_jxql = {} //净需求量=毛需求量-(上期库存余量-安全库存量)
            let a_jhscl = {} //计划生产量
            let a_gsbzl = {} //过剩/不足量=计划生产量-净需求量
            let a_yjqmkc = {} //预计期末库存=上期库存余量+计划生产量-毛需求量

            //当前库存
            sql = "select sum(quantity) as qty from erp_stock where pn_id=" + db.escape(p.product_id) + " and is_deleted=0"
            r = await db.query(sql, []);
            let stock_current = r.rows[0].qty;
            let pre_sqkcyl = stock_current; // TODO:未推演至期初库存

            //逐周期运算
            for (let i in periods) {
                let period = periods[i];
                let find_forecasts = forecasts.filter(f => f.product_id == p.product_id && f.start_date == period.start.format("yyyy-MM-dd"))
                let total_forecast = 0
                for(let fc of find_forecasts){
                    total_forecast=Number(fc.quantity);
                }
                let find_orders = orders.filter(o => o.product_id == p.product_id && o.delivery_date == period.start.format("yyyy-MM-dd"))
                let total_order = 0
                for(let order of find_orders){
                    total_order=Number(order.qty);
                }
                let find_plans = plans.filter(pl => pl.product_id == p.product_id && pl.start_date == period.start.format("yyyy-MM-dd"))
                let total_plan = 0
                for(let plan of find_plans){
                    total_plan=Number(plan.qty);
                }

                let ddxql = total_order //订单需求量
                let ycxql = total_forecast //预测需求量
                let mxql = ddxql + ycxql //毛需求量=订单需求量+预测需求量
                let sqkcyl = pre_sqkcyl //上期库存余量
                let aqkcl = p.stock_safe //安全库存量
                let jxql = mxql - (sqkcyl - aqkcl) //净需求量=毛需求量-(上期库存余量-安全库存量)
                let jhscl = total_plan //计划生产量
                let gsbzl = jhscl - jxql //过剩/不足量=计划生产量-净需求量
                let yjqmkc = sqkcyl + jhscl - mxql //预计期末库存=上期库存余量+计划生产量-毛需求量
                pre_sqkcyl = yjqmkc

                a_ddxql['p_' + i] = ddxql
                a_ycxql['p_' + i] = ycxql
                a_mxql['p_' + i] = mxql
                a_sqkcyl['p_' + i] = sqkcyl
                a_aqkcl['p_' + i] = aqkcl
                a_jxql['p_' + i] = jxql
                a_jhscl['p_' + i] = jhscl
                a_gsbzl['p_' + i] = gsbzl
                a_yjqmkc['p_' + i] = yjqmkc
            }
            rows = rows.concat([
                {
                    product_code: p.product_code,
                    product_name: p.product_name,
                    item_name: '订单需求量',
                    ...a_ddxql
                },
                {
                    product_code: p.product_code,
                    product_name: p.product_name,
                    item_name: '预测需求量',
                    ...a_ycxql
                },
                {
                    product_code: p.product_code,
                    product_name: p.product_name,
                    item_name: '毛需求量',
                    ...a_mxql
                },
                {
                    product_code: p.product_code,
                    product_name: p.product_name,
                    item_name: '上期库存余量',
                    ...a_sqkcyl
                },
                {
                    product_code: p.product_code,
                    product_name: p.product_name,
                    item_name: '安全库存量',
                    ...a_aqkcl
                },
                {
                    product_code: p.product_code,
                    product_name: p.product_name,
                    item_name: '净需求量',
                    ...a_jxql
                },
                {
                    product_code: p.product_code,
                    product_name: p.product_name,
                    item_name: '计划生产量',
                    ...a_jhscl
                },
                {
                    product_code: p.product_code,
                    product_name: p.product_name,
                    item_name: '[过剩/不足量]',
                    ...a_gsbzl
                },
                {
                    product_code: p.product_code,
                    product_name: p.product_name,
                    item_name: '预计期末库存',
                    ...a_yjqmkc
                }
            ])
        }

        return {
            rows: rows,
            columns: columns
        }
    },
    getdata_capacity_fullfill: async function(filter){
        let period_type = '天'
        let period_count = 10
        if (filter && filter.children) {
            find = filter.children.find(c => c.left.field == 'period_type');
            if (find && find.right) period_type = find.right;
            find = filter.children.find(c => c.left.field == 'period_count');
            if (find && find.right) period_count = find.right;
        }
        //1.根据周期分期
        let periods_set = this.get_periods_set(period_type,period_count);
        let periods = periods_set.periods;
        let total_start_day = periods_set.total_start_day;
        let total_end_day = periods_set.total_end_day;
        //2.从主生产计划中取出待交货产品列表
        let sql = "select id as product_id, code as product_code, name as product_name, stock_safe from mes_pn where id in \
                (select product_id from erp_main_produce_plan where period_type=" + db.escape(period_type) + " and is_deleted=0 \
                and start_date>="+ db.escape(total_start_day) + "and delivery_date<=" + db.escape(total_end_day) + ")"
        let r = await db.query(sql, []);
        let products = r.rows;
        //3.总时间范围内的产能、生产计划
        sql = "select * from erp_standard_capacity where is_deleted=0 \
            and (start_date<="+ db.escape(total_start_day) + " and end_date>="+ db.escape(total_start_day) + " \
                or start_date<=" + db.escape(total_end_day) + " and end_date>=" + db.escape(total_end_day) + ")";
        r = await db.query(sql, []);
        let capacities = r.rows; //产能
        sql = "select * from erp_main_produce_plan where period_type=" + db.escape(period_type) + " and is_deleted=0 \
        and start_date>="+ db.escape(total_start_day) + "and delivery_date<=" + db.escape(total_end_day);
        r = await db.query(sql, []);
        let plans = r.rows; //计划

        //4.逐产品，逐周期，进行产能运算
        let columns = [
            {
                label: "产品编码",
                name: "product_code"
            },
            {
                label: "产品名",
                name: "product_name"
            },
            {
                label: "数值项(" + period_type + ")",
                name: "item_name"
            }
        ]
        for (let i in periods) {
            let p = periods[i]
            let label = period_type == '月' ? (p.start.getFullYear() + '-' + (p.start.getMonth() + 1)) : ((p.start.getMonth() + 1) + '-' + p.start.getDate())
            columns.push({ label: label, name: 'p_' + i })
        }
        let rows = []
        for (let i in products) {
            let p = products[i]
            let a_zscjhl = {} //主计划生产量
            let a_bzcn = {} //标准产能
            let a_cncy = {} //产能差异

            //console.log(capacities);
            //逐周期运算
            for (let i in periods) {
                let period = periods[i];
                let find_caps = capacities.filter(cap => cap.product_id == p.product_id && cap.start_date <= period.start.format("yyyy-MM-dd") && cap.end_date >= period.start.format("yyyy-MM-dd"))
                let total_cap = 0
                for(let cap of find_caps){
                    total_cap+=Number(cap.standard_qty);
                }
                let find_plans = plans.filter(pl => pl.product_id == p.product_id && pl.start_date == period.start.format("yyyy-MM-dd"))
                let total_plan = 0
                for(let plan of find_plans){
                    total_plan=Number(plan.qty);
                }
                let zscjhl = total_plan //主生产计划量
                let bzcn = total_cap //标准产能

                a_zscjhl['p_' + i] = zscjhl
                a_bzcn['p_' + i] = bzcn
                a_cncy['p_' + i] = bzcn - zscjhl
            }
            rows = rows.concat([
                {
                    product_code: p.product_code,
                    product_name: p.product_name,
                    item_name: '主生产计划量',
                    ...a_zscjhl
                },
                {
                    product_code: p.product_code,
                    product_name: p.product_name,
                    item_name: '标准产能',
                    ...a_bzcn
                },
                {
                    product_code: p.product_code,
                    product_name: p.product_name,
                    item_name: '[产能过剩/不足]',
                    ...a_cncy
                }
            ])
        }

        return {
            rows: rows,
            columns: columns
        }
    },
    getdata_capacity_load: async function(filter){
        let period_type = '天'
        let period_count = 10
        if (filter && filter.children) {
            find = filter.children.find(c => c.left && c.left.field == 'period_type');
            if (find && find.right) period_type = find.right;
            find = filter.children.find(c => c.left && c.left.field == 'period_count');
            if (find && find.right) period_count = find.right;
        }
        //1.根据周期分期
        let periods_set = this.get_periods_set(period_type,period_count);
        let periods = periods_set.periods;
        let total_start_day = periods_set.total_start_day;
        let total_end_day = periods_set.total_end_day;
        //2.从主生产计划中取出工作中心列表
        let sql = "select id,name,code from mes_resource where id in \
                (select workcenter_id from erp_main_produce_plan where period_type=" + db.escape(period_type) + " and is_deleted=0 \
                and start_date>="+ db.escape(total_start_day) + "and delivery_date<=" + db.escape(total_end_day) + ")"
        let r = await db.query(sql, []);
        let workcenters = r.rows;
        //3.总时间范围内的产能、生产计划
        sql = "select * from erp_standard_capacity where is_deleted=0 \
            and (start_date<="+ db.escape(total_start_day) + " and end_date>="+ db.escape(total_start_day) + " \
                or start_date<=" + db.escape(total_end_day) + " and end_date>=" + db.escape(total_end_day) + ")";
        r = await db.query(sql, []);
        let capacities = r.rows; //产能
        sql = "select * from erp_main_produce_plan where period_type=" + db.escape(period_type) + " and is_deleted=0 \
        and start_date>="+ db.escape(total_start_day) + "and delivery_date<=" + db.escape(total_end_day);
        r = await db.query(sql, []);
        let plans = r.rows; //计划
        //产能相关的产品
        sql = "select id,name,code from mes_pn where id in \
                (select product_id from erp_main_produce_plan where period_type=" + db.escape(period_type) + " and is_deleted=0 \
                and start_date>="+ db.escape(total_start_day) + "and delivery_date<=" + db.escape(total_end_day) + ")"
        r = await db.query(sql, []);
        let products = r.rows;

        //console.log(workcenters)
        //4.逐工作中心，逐周期，进行库存余量运算，余量不低于安全库存，则满足本周起交付需求
        let columns = [
            {
                label: "工作中心编码",
                name: "wc_code"
            },
            {
                label: "产品编码",
                name: "product_code"
            },
            {
                label: "项目名",
                name: "item_name"
            }
        ]
        for (let i in periods) {
            let p = periods[i]
            let label = period_type == '月' ? (p.start.getFullYear() + '-' + (p.start.getMonth() + 1)) : ((p.start.getMonth() + 1) + '-' + p.start.getDate())
            columns.push({ label: label, name: 'p_' + i })
        }
        let rows = []
        for(let i in workcenters){
            let wc = workcenters[i];
            let find_caps = capacities.filter(cap => cap.workcenter_id == wc.id);
            let wc_products = [];
            for(cap of find_caps){
                if(!wc_products.find(p=>p.id==cap.product_id)){
                    let product=products.find(p=>p.id==cap.product_id)
                    wc_products.push(product);
                }
            }
            let p_loads=[];
            for (let i in wc_products) {
                let p = wc_products[i]
                let a_load = []
                let p_load = []
                //逐周期运算
                for (let i in periods) {
                    let period = periods[i];
                    let find_cap = capacities.find(cap => cap.workcenter_id == wc.id && cap.product_id == p.id && cap.start_date <= period.start.format("yyyy-MM-dd") && cap.end_date >= period.start.format("yyyy-MM-dd"))
                    let find_plan = plans.find(pl => pl.workcenter_id == wc.id && pl.product_id == p.id && pl.start_date == period.start.format("yyyy-MM-dd"))
                    let standard_qty = find_cap?Number(find_cap.standard_qty):0
                    let percent = find_cap?Number(find_cap.percent):0
                    let plan_qty = find_plan?Number(find_plan.qty):0  
                    let load = (100*plan_qty/standard_qty); 
                    a_load['p_' + i] = load==0?'0%':(load.toFixed(2)+"%")
                    p_load[i]={
                        standard_qty,percent,plan_qty
                    }
                }
                p_loads.push(p_load);
                rows = rows.concat([
                    {
                        wc_code: wc.code,
                        product_code: p.code,
                        item_name: p.name,
                        ...a_load
                    }
                ])
            }
            //逐周期合并各产品汇总符合
            let a_total_load=[]
            for (let i in periods) {
                let total_load=0
                for(let p_load of p_loads){
                    let load = p_load[i];
                    total_load+= (load.percent * load.plan_qty / load.standard_qty)
                }
                a_total_load['p_'+i]=(total_load==0?'0%':(total_load.toFixed(2)+"%"))
            }
            rows = rows.concat([
                {
                    wc_code: wc.code,
                    product_code: '',
                    item_name: "小计",
                    ...a_total_load
                }
            ])
        }

        return {
            rows: rows,
            columns: columns
        }
    },
    getdata_capacity_load_chart: async function(filter){
        let load = await this.getdata_capacity_load(filter);
        let r_rows=[]
        let r_cols=[]
        for(let col of load.columns){
            if(col.name.substr(0,2)=='p_'){
                let values={}
                for(let row of load.rows.filter(r=>r.item_name=='小计')){
                    let value=row[col.name];
                    values[row.wc_code]=Number(value.substr(0,value.length-1))
                    if(!r_cols.some(c=>c.name==row.wc_code)){
                        r_cols.push({label:row.wc_code,name:row.wc_code})
                    }
                }
                r_rows.push({
                    date: col.label,
                    ...values
                })
            }
        }
        return {
            rows:r_rows,
            columns:r_cols
        }
    },
    get_periods_set: function(period_type,period_count){
        let periods = []
        let total_start_day;
        let total_end_day;
        switch (period_type) {
            case '月':
                {
                    let day = new Date();
                    for (let i = 0; i < period_count; i++) {
                        let month = day.getMonth();
                        let year = day.getFullYear();
                        let start_day = new Date(year, month, 1);
                        let end_day = new Date(year, month + 1, 0);
                        start_day.setHours(0, 0, 0, 0);
                        end_day.setHours(23, 59, 59, 999);
                        periods.push({ start: start_day, end: end_day })
                        day.setDate(end_day.getDate() + 15)
                        if (i == 0) total_start_day = start_day;
                        if (i == period_count - 1) total_end_day = end_day;
                    }
                }
                break;
            case '周':
                {
                    let day = new Date().getDay();
                    let deltaDay = (day == 0 ? 6 : (day - 1));
                    let monday = new Date();
                    monday.setDate(new Date().getDate() - deltaDay);
                    for (let i = 0; i < period_count; i++) {
                        let start_day = new Date();
                        let end_day = new Date();
                        start_day.setDate(monday.getDate() + i * 7);
                        end_day.setDate(start_day.getDate() + 7);
                        start_day.setHours(0, 0, 0, 0);
                        end_day.setHours(23, 59, 59, 999);
                        periods.push({ start: start_day, end: end_day })
                        if (i == 0) total_start_day = start_day;
                        if (i == period_count - 1) total_end_day = end_day;
                    }
                }
                break;
            default:
                for (let i = 0; i < period_count; i++) {
                    let start_day = new Date();
                    start_day.setDate(start_day.getDate() + i);
                    let end_day = new Date();
                    end_day.setDate(end_day.getDate() + i);
                    start_day.setHours(0, 0, 0, 0);
                    end_day.setHours(23, 59, 59, 999);
                    periods.push({ start: start_day, end: end_day })
                    if (i == 0) total_start_day = start_day;
                    if (i == period_count - 1) total_end_day = end_day;
                }
                break;
        }
        return {
            periods: periods,
            total_start_day: total_start_day,
            total_end_day: total_end_day
        };
    }
}

Date.prototype.format = function (fmt) {
    var o = {
        "M+": this.getMonth() + 1,                 //月份 
        "d+": this.getDate(),                    //日 
        "h+": this.getHours(),                   //小时 
        "m+": this.getMinutes(),                 //分 
        "s+": this.getSeconds(),                 //秒 
        "q+": Math.floor((this.getMonth() + 3) / 3), //季度 
        "S": this.getMilliseconds()             //毫秒 
    };
    if (/(y+)/.test(fmt)) {
        fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
    }
    for (var k in o) {
        if (new RegExp("(" + k + ")").test(fmt)) {
            fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
        }
    }
    return fmt;
}

module.exports = { service }